/**
 * @author lteng
 * @time 2022-06-14 16:37:45 
 */

package liteng.study.cn.stuscore.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 
 */
public abstract class BaseDao {
	
	
	// 用户名
	private final String USERNAME = "root";
	// 密码
	private final String PASSWORD = "123qwe";
	// 链接地址
	private final String URL = "jdbc:mysql://192.168.31.129:3306/stuscore?" + "useUnicode=true&" + "characterEncoding=utf-8&"
			+ "useSSL=true&" + "serverTimezone=UTC";
	// 驱动
	private final String DRIVER = "com.mysql.cj.jdbc.Driver";

	/**
	 * 建立连接对象
	 * 
	 * @return 连接对象
	 * @throws ClassNotFoundException
	 * @throws SQLException
	 */
	public Connection getConn() throws ClassNotFoundException, SQLException {
		Class.forName(DRIVER);
		Connection conn = DriverManager.getConnection(URL,USERNAME, PASSWORD);

		return conn;

	}

	/**
	 * 创建statement对象
	 * 
	 * @param conn 数据库连接
	 * @param sql  sql语句
	 * @return statement对象
	 * @throws SQLException
	 */
	public PreparedStatement createPstmt(Connection conn, String sql) throws SQLException {
		return conn.prepareStatement(sql);

	}

	/**
	 * 
	 * @param sql
	 * @param params
	 * @return 结果集对象
	 */
	public ResultSet getResl(String sql, Object... params) {
		ResultSet resl = null;

		Connection conn;
		try {
			conn = getConn();

			PreparedStatement pstmt = createPstmt(conn, sql);
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject((i + 1), params[i]);
			}
			resl = pstmt.executeQuery();
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return resl;

	}

	/**
	 * 关闭连接对象
	 * 
	 * @param conn
	 * @param stmt
	 * @param resl
	 * @throws SQLException
	 */
	public void close(Connection conn, Statement stmt) throws SQLException {
		if (stmt != null) {
			stmt.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * 关闭连接对象
	 * 
	 * @param conn
	 * @param stmt
	 * @param resl
	 * @throws SQLException
	 */
	public void close(Connection conn, Statement stmt, ResultSet resl) throws SQLException {
		if (resl != null) {
			resl.close();
		}
		if (stmt != null) {
			stmt.close();
		}
		if (conn != null) {
			conn.close();
		}
	}

	/**
	 * 数据更新操作
	 * 
	 * @param sql
	 * @param params
	 */
	public void excuteUpdate(String sql, Object... params) {
		Connection conn;
		try {
			conn = getConn();
			PreparedStatement pstmt = createPstmt(conn, sql);
			for (int i = 0; i < params.length; i++) {
				pstmt.setObject((i + 1), params[i]);
			}
			pstmt.executeUpdate();
			close(conn, pstmt);

		} catch (ClassNotFoundException e) {
			// TODO: handle exception
			e.printStackTrace();

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * add
	 * @param sql
	 * @param params
	 */
	public void add(String sql, Object... params) {
		excuteUpdate(sql, params);
	}

	/**
	 * update
	 * @param sql
	 * @param params
	 */
	public void update(String sql, Object... params) {
		excuteUpdate(sql, params);
	}

	/**
	 * delete
	 * @param sql
	 * @param params
	 */
	public void delete(String sql, Object... params) {
		excuteUpdate(sql, params);
	}

	/**
	 * 获取数据总量
	 * @param sql
	 * @param params
	 * @return
	 */
	public int qryCount(String sql, Object... params) {
		int count = 0;
		ResultSet rs = getResl(sql, params);
		try {
			while(rs.next()) {
				count = rs.getInt("count(*)");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
}
